# coding=utf-8
"""
    处理数据库恢复命令
    方便恢复数据库

    命令组成:
        1. 链接数据库服务
        2. 创建数据库 ( CREATE DATABASE xxx; )
        3. 恢复命令 ( mysql -u root -p {password} xxx < xxx.sql)
"""
import os

from main import IGNORE_DBS, BACKUP_FOLDER
from main.do_utils import sql_log


# @Time    :  2024-07-11 10:13:34
# @Author  :  jiangtong
# @Email   :  gingerqgyy@outlook.com
# @Project :  mysql_backup
# @File    :  do_recover


class SQLRecover:
    __file = 'recover_sql.txt'

    # 初始化, 恢复脚本可以放在别的位置 :
    def __init__(self, file_name=None):
        self.command_list = []
        self.command_list_create = []
        self.command_list_recover = []
        self.dbs = []

        # 恢复文件 :
        file_name = file_name if file_name else self.__file
        self.file_name = os.sep.join([BACKUP_FOLDER, file_name]).replace(os.sep, '/')
        self.file = file_name

    # 链接命令 : ( 用于调试 )
    def mysql_connect(self, db_conf):
        """
        链接数据库, 查询目标书库的所有数据库名
        生成链接命令, 存储到 command_list 中
        :param db_conf: 目标数据库链接信息
        :return self.dbs: 所有数据库的库名列表, ( 此返回只是为了方便调试 )
        """
        os.environ['MYSQL_PWD'] = db_conf.get('password')
        cmd_dbs = f"mysql -h {db_conf['host']} -u {db_conf['user']} -P {db_conf['port']} -e \"show databases;\" -B"
        self.command_list.append(f"mysql -h {db_conf['host']} -u {db_conf['user']} -P {db_conf['port']} -p{db_conf['password']}")
        self.dbs = [line.strip() for line in os.popen(cmd_dbs).read().splitlines() if line.strip() not in IGNORE_DBS]
        sql_log.info(f'mysql数据库 : << {db_conf.get('host')} >> 的所有库有: {self.dbs}')
        return self.dbs

    def mysql_connect_info(self, db_conf):
        """
        只是用于生成连接命令
        :param db_conf:
        :return:
        """
        self.command_list.append(f"mysql -h {db_conf['host']} -u {db_conf['user']} -P {db_conf['port']} -p{db_conf['password']}")
        pass

    # 创建数据库命令 :
    def mysql_create(self, db_name):
        cmd_create = f'CREATE DATABASE {db_name};'
        self.command_list_create.append(cmd_create)
        sql_log.info(f'mysql 数据库 << {db_name} >> 的创建命令记录成功')

    # 创建数据库和恢复命令 : ( source 方式恢复 )
    def mysql_recover(self, db_conf, db_name, db_backup_time, backup_folder):
        backup_folder = backup_folder if backup_folder else BACKUP_FOLDER
        backup_file = os.sep.join([backup_folder, f'{db_name}_{db_backup_time}.sql']).replace(os.sep, '/')
        cmd_use_db = f'use {db_name};'
        cmd_recover = f'source {backup_file};'
        self.command_list_recover.append(cmd_use_db)
        self.command_list_recover.append(cmd_recover)
        sql_log.info(f'mysql 数据库: << {db_conf.get('host')} --> {db_name} >> 的恢复命令记录成功 ----> source 方式')

    # 创建数据库和恢复命令 : ( cmd 方式恢复 )
    def mysql_recover_cmd(self, db_conf, db_name, db_backup_time, backup_folder):
        backup_folder = backup_folder if backup_folder else BACKUP_FOLDER
        backup_file = os.sep.join([backup_folder, f'{db_name}_{db_backup_time}.sql']).replace(os.sep, '/')
        # --force 的作用是忽略错误 ( 实测 test 环境的数据库中存在命名约束了唯一性, 但是数据并不唯一的记录, 会导致写入失败 ... )
        cmd = f'mysql --force -h {db_conf['host']} -u {db_conf['user']} -p{db_conf['password']} -P {db_conf['port']} {db_name} < {backup_file}'
        self.command_list_recover.append(cmd)
        sql_log.info(f'mysql 数据库: << {db_conf.get('host')} --> {db_name} >> 的恢复命令记录成功 ----> cmd 方式')

    # 命令聚合存储 :
    def mysql_recover_file(self, backup_folder):
        """
        将恢复命令存放到 txt 文件中
        :param backup_folder: 备份目录, 如果传递了该参数, 则使用该参数指定的目录进行备份, 如果为传递则使用默认目录, 自动执行时使用的是默认目录
        :return: None
        """
        explain = ['如果数据库存在则跳过所有 CREATE 语句']

        backup_folder = backup_folder if backup_folder else BACKUP_FOLDER
        recover_file = os.sep.join([backup_folder, self.file]).replace(os.sep, '/')
        sql_log.info('开始创建恢复文件, 并存储恢复命令:')
        with open(recover_file, mode='w', encoding='utf8') as f:
            cmd_list = explain + self.command_list + self.command_list_create + self.command_list_recover
            cmd_str = '\n\n'.join(cmd_list)
            f.write(cmd_str)
        sql_log.info('完成创建恢复文件, 完成存储恢复命令.')


if __name__ == '__main__':
    DB_INFO_OWN = {'host': '121.36.110.11', 'user': 'root', 'password': '521125', 'port': '33060'}
    """
    db_recover = SQLRecover(file_name='recover-source.txt')

    db_s = db_recover.mysql_connect(DB_INFO_OWN)

    for db in db_s:
        db_recover.mysql_recover(DB_INFO_OWN, db, '000', backup_folder=r'D:/Useless/Share/SQL/custom_backup')
        db_recover.mysql_create(db)

    db_recover.mysql_recover_file()
    # """

    # """
    db_recover = SQLRecover(file_name='recover-cmd.txt')

    db_s = db_recover.mysql_connect(DB_INFO_OWN)

    for db in db_s:
        db_recover.mysql_recover_cmd(DB_INFO_OWN, db, '000', backup_folder=r'D:/Useless/Share/SQL/custom_backup')
        db_recover.mysql_create(db)

    db_recover.mysql_recover_file()
    # """
